package examinee;

import utils.DBConnection;
import utils.TextUtils;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;
import java.util.ArrayList;

/**
 * Created by IntelliJ IDEA.
 * User: Shawn
 * Date: 2009-6-10
 * Time: 22:56:40
 */
public class Examinee {

  public static long addExamniee(Connection con, ExamineeInfo info) throws SQLException {
    String sql = "INSERT INTO examinee VALUES (NULL, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setString(1, info.getEname());
    ps.setInt(2, info.getGender());
    ps.setString(3, info.getPxjg());
    ps.setString(4, info.getSfzh());
    ps.setString(5, info.getJszh());
    ps.setString(6, info.getZjcx());
    ps.setString(7, info.getZkzh());
    ps.setString(8, info.getKskm());
    ps.setLong(9, info.getKsrq());
    ps.setInt(10, info.getScore());
    ps.executeUpdate();
    return DBConnection.getLastID(con);
  }

  public static void modiExaminee(Connection con, ExamineeInfo info) throws SQLException {
    String sql = "UPDATE examinee SET ename=?, gender=?, pxjg=?, sfzh=?, jszh=?, zjcx=?, zkzh=?, kskm=?, ksrq=?, score=? WHERE eid=? LIMIT 1";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setString(1, info.getEname());
    ps.setInt(2, info.getGender());
    ps.setString(3, info.getPxjg());
    ps.setString(4, info.getSfzh());
    ps.setString(5, info.getJszh());
    ps.setString(6, info.getZjcx());
    ps.setString(7, info.getZkzh());
    ps.setString(8, info.getKskm());
    ps.setLong(9, info.getKsrq());
    ps.setInt(10, info.getScore());
    ps.setLong(11, info.getEid());
    ps.executeUpdate();
  }

  public static void setScore(Connection con, long eid, int score) throws SQLException {
    String sql = "UPDATE examinee SET score=? WHERE eid=? LIMIT 1";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setInt(1, score);
    ps.setLong(2, eid);
    ps.executeUpdate();
  }

  public static void delExaminee(Connection con, long eid) throws SQLException {
    String sql = "DELETE FROM examinee WHERE eid=" + eid + " LIMIT 1";
    con.createStatement().executeUpdate(sql);
  }

  public static ExamineeInfo getExaminee(Connection con, long eid) throws SQLException {
    String sql = "SELECT * FROM examinee WHERE eid=" + eid + " LIMIT 1";
    ResultSet rs = con.createStatement().executeQuery(sql);
    return rs.next() ? rs2Info(rs) : new ExamineeInfo();
  }

  public static ExamineeInfo getExaminee(Connection con, String zkzh, long ksrq) throws SQLException {
    String sql = "SELECT * FROM examinee WHERE zkzh=? AND ksrq=? LIMIT 1";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setString(1, zkzh);
    ps.setLong(2, ksrq);
    ResultSet rs = ps.executeQuery();
    return rs.next() ? rs2Info(rs) : new ExamineeInfo();
  }

  public static List<ExamineeInfo> getExamineeList(Connection con, SearchInfo info, long from, long length) throws Exception {
    String sql = "SELECT * FROM examinee" + getSearchSql(info) + " LIMIT " + from + ", " + length;
    ResultSet rs = con.createStatement().executeQuery(sql);

    List<ExamineeInfo> list = new ArrayList<ExamineeInfo>();
    while (rs.next()) list.add(rs2Info(rs));
    return list;
  }

  public static List<ExamineeInfo> getExamineeList(Connection con, long from, long to) throws Exception {
    String sql = "SELECT * FROM examinee WHERE ksrq>=? AND ksrq<=?";
    PreparedStatement ps = con.prepareStatement(sql);
    ps.setLong(1, from);
    ps.setLong(2, to == 0 ? System.currentTimeMillis() : to);
    ResultSet rs = ps.executeQuery();

    ArrayList<ExamineeInfo> list = new ArrayList<ExamineeInfo>();
    while (rs.next()) list.add(rs2Info(rs));
    return list;
  }

  public static List<ExamineeInfo> getTodayExamineeList(Connection con, String zkzh) throws Exception {
    String sql = "SELECT * FROM examinee WHERE zkzh LIKE '%" + zkzh + "%' AND ksrq=" + TextUtils.getZeroTime(System.currentTimeMillis());
    ResultSet rs = con.createStatement().executeQuery(sql);

    List<ExamineeInfo> list = new ArrayList<ExamineeInfo>();
    while (rs.next()) list.add(rs2Info(rs));
    return list;
  }

  public static long getExamineeSum(Connection con, SearchInfo info) throws Exception {
    String sql = "SELECT COUNT(*) AS sum FROM examinee" + getSearchSql(info);
    ResultSet rs = con.createStatement().executeQuery(sql);
    return rs.next() ? rs.getInt("sum") : 0;
  }

  public static List<String> getPXJGList(Connection con) throws SQLException {
    String sql = "SELECT * FROM examinee GROUP BY pxjg";
    ResultSet rs = con.createStatement().executeQuery(sql);

    List<String> list = new ArrayList<String>();
    while (rs.next()) list.add(rs.getString("pxjg"));
    return list;
  }

  private static String getSearchSql(SearchInfo info) throws Exception {
    String sql = " WHERE ename LIKE '%" + TextUtils.toUtf8(info.getEname()) + "%'" +
            " AND zkzh LIKE '%" + info.getZkzh() + "%'" +
            " AND sfzh LIKE '%" + info.getSfzh() + "%'" +
            " AND pxjg LIKE '%" + TextUtils.toUtf8(info.getPxjg()) + "%'";

    if (info.getScore() == ExamineeVar.SCORE_PASS) sql += " AND score>=" + ExamineeVar.PASS_SCORE;
    else if (info.getScore() == ExamineeVar.SCORE_NOT_PASS) sql += " AND score<" + ExamineeVar.PASS_SCORE
            + " AND score!=-1";
    else if (info.getScore() == ExamineeVar.SCORE_TESTED) sql += " AND score!=-1";
    else if (info.getScore() == ExamineeVar.SOCRE_NOT_TEST) sql += " AND score=-1";
    else sql += "";

    if (info.getFrom().length() != 0) sql += " AND ksrq>=" + TextUtils.GetMilsecTime(info.getFrom());
    if (info.getTo().length() != 0) sql += " AND ksrq<=" + TextUtils.GetMilsecTime(info.getTo());

    return sql;
  }

  private static ExamineeInfo rs2Info(ResultSet rs) throws SQLException {
    ExamineeInfo info = new ExamineeInfo();
    info.setEid(rs.getLong("eid"));
    info.setEname(rs.getString("ename"));
    info.setGender(rs.getInt("gender"));
    info.setPxjg(rs.getString("pxjg"));
    info.setSfzh(rs.getString("sfzh"));
    info.setJszh(rs.getString("jszh"));
    info.setZjcx(rs.getString("zjcx"));
    info.setZkzh(rs.getString("zkzh"));
    info.setKskm(rs.getString("kskm"));
    info.setKsrq(rs.getLong("ksrq"));
    info.setScore(rs.getInt("score"));
    return info;
  }
}
